const db = require('./db')

const character = {
  async create(data) {
    const conn = await db.getConnection();
    try {
      await conn.beginTransaction();

      const [result] = await conn.execute(
        `INSERT INTO virtual_character 
      (author_id, name, image_url, description, background, preferences, extra_info) 
      VALUES (?, ?, ?, ?, ?, ?, ?)`,
        [
          data.author_id,  // 直接使用从中间件获取的授权用户ID
          data.name,       // 结构正确性调整
          data.image_url,
          data.description,
          data.background,
          JSON.stringify(data.preferences),
          data.extra_info ? JSON.stringify(data.extra_info) : null
        ]
      );

      await conn.commit();
      return { id: result.insertId };
    } catch (error) {
      await conn.rollback();
      console.error('Database Error:', error);  // 添加错误日志
      throw new Error('数据库操作失败');
    } finally {
      conn.release();
    }
  },
  async getById(id) {
    const [rows] = await db.execute(
      `SELECT 
        id, author_id, name, image_url, 
        description, background, 
        JSON_UNQUOTE(preferences) as preferences,
        JSON_UNQUOTE(extra_info) as extra_info,
        popularity, created_at, updated_at 
      FROM virtual_character WHERE id = ?`,
      [id]
    );
    return rows[0] || null;
  },

  async updateById(id, data) {
    const conn = await db.getConnection();
    try {
      const updateFields = [];
      const params = [];

      Object.entries(data).forEach(([key, value]) => {
        if (['preferences', 'extra_info'].includes(key)) {
          updateFields.push(`${key} = ?`);
          params.push(JSON.stringify(value));
        } else {
          updateFields.push(`${key} = ?`);
          params.push(value);
        }
      });

      params.push(id);

      await conn.execute(
        `UPDATE virtual_character 
      SET ${updateFields.join(', ')} 
      WHERE id = ?`,
        params
      );

      return getById(id);
    } finally {
      conn.release();
    }
  },
  async deleteById(id) {
    await db.execute(
      'DELETE FROM virtual_character WHERE id = ?',
      [id]
    );
  },
  async listByCursor(cursor, pageSize = 10, userId = null) {
    // 参数验证
    const parsedPageSize = Math.max(1, parseInt(pageSize, 10) || 10);
    const limitSize = parsedPageSize + 1;

    let cursorId = null;
    if (cursor !== null && cursor !== undefined) {
      const parsed = parseInt(cursor, 10);
      if (!isNaN(parsed) && parsed >= 0) cursorId = parsed;
    }

    // 验证用户ID
    let validUserId = null;
    if (typeof userId === 'number' && userId > 0) {
      validUserId = userId;
    } else if (typeof userId === 'string' && /^\d+$/.test(userId)) {
      validUserId = parseInt(userId, 10);
    }

    // 构建动态WHERE条件
    const whereConditions = [];
    const params = [];

    if (cursorId !== null) {
      whereConditions.push('vc.id < ?');
      params.push(cursorId);
    }

    if (validUserId !== null) {
      whereConditions.push('vc.author_id = ?');
      params.push(validUserId);
    }

    // 组合查询语句
    const whereClause = whereConditions.length > 0
      ? `WHERE ${whereConditions.join(' AND ')}`
      : '';

    const query = `
    SELECT 
      vc.id,
      vc.name,
      vc.image_url,
      vc.author_id  -- 返回用户ID用于验证
    FROM virtual_character vc
    ${whereClause}
    ORDER BY vc.id DESC
    LIMIT ?
    `;

    params.push(limitSize);

    // 执行查询
    const [rows] = await db.query(query, params);

    // 处理结果
    const hasMore = rows.length > parsedPageSize;
    const items = hasMore ? rows.slice(0, parsedPageSize) : rows;
    // 返回结构化结果
    return {
      items,
      next_cursor: items.length ? items[items.length - 1].id : null,
      has_more: hasMore,
      page_size: parsedPageSize
    };
  }
}

module.exports = character;